-- @owner: wan005
-- @date: 2024-08-12
-- @testpoint: substr()函数作为对象名的测试,部分测试点合理报错

--prestep1:切换B库;expect:执行成功
@conn PrimaryDbBmode;
show sql_compatibility;

--step1:作为列名;expect:成功
drop table if exists substr_test cascade;
create table substr_test(substr text);
insert into substr_test values('0');

--step2:作为索引名;expect:成功
create index on substr_test(substr);

--step3:作为表名;expect:成功
drop table if exists substr cascade;
create table substr(substr text);

--step4:作为数据库名;expect:成功
drop database if exists substr;
create database substr;
drop database substr;

--step5:作为外部数据源名;expect:成功
drop data source if exists substr;
create data source substr;
drop data source substr;

--step6:作为自定义函数名;expect:报错
create or replace function substr(c1 int)
returns text
as  $$
begin
    return c1;
end;
$$language plpgsql;
/

--step7:作为存储过程名;expect:报错
drop table if exists t_substr_0019;
create table t_substr_0019(c1 text);
CREATE OR REPLACE PROCEDURE substr(P1 IN text) AS
BEGIN
insert into t_substr_0019 values ('00001');
raise info '测试substr作为存储过程名';
END;
/

--step8:作为角色名;expect:成功
drop role if exists substr;
create role substr with password 'gauss@123' valid until '2001-12-12';
drop role substr;

--step9:作为用户组名;expect:成功
drop group if exists substr;
create group substr with password 'gauss@123';
drop group substr;

--prestep1:切换B库;expect:执行成功
@conn PrimaryDbBmode;
show sql_compatibility;

--step10:作为模式名;expect:成功
drop schema if exists substr;
create schema substr;
drop schema substr;

--prestep1:切换B库;expect:执行成功
@conn PrimaryDbBmode;
show sql_compatibility;

--step11:作为序列名;expect:成功
create sequence public.substr start 100 cache 50;
drop sequence public.substr;

--prestep1:切换B库;expect:执行成功
@conn PrimaryDbBmode;
show sql_compatibility;

--step12:作为用户名;expect:成功
drop user if exists substr;
CREATE USER substr PASSWORD 'Bigdata@123';
drop user substr;

--prestep1:切换B库;expect:执行成功
@conn PrimaryDbBmode;
show sql_compatibility;

--step13:作为游标名;expect:成功
drop table if exists substr_test cascade;
create table substr_test(cid int,fid int);
start transaction;
cursor substr for select * from substr_test order by 1;
close substr;
end;

--prestep1:切换B库;expect:执行成功
@conn PrimaryDbBmode;
show sql_compatibility;

--step14:清理环境;expect:成功
drop table if exists substr_test cascade;
drop table if exists substr cascade;
drop table if exists t_substr_0019;